package com.sg.common.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartRequest;

public class FileUtil {

	private static final String EXTENSION_XLS = "xls";
	private static final String EXTENSION_XLSX = "xlsx";

	public static void readExcel(HttpServletRequest request,
			List<Integer> idFieldList, List<Integer> valueFieldList,
			List<Map<Integer, String>> values,
			Map<Integer, String> propertyLocations, Map<String, String> fieldMsg) {
		MultipartRequest multipartRequest = (MultipartRequest) request;
		MultipartFile excelFile = multipartRequest.getFile("_impFile");
		// String fh = request.getParameter("_fromNo");
		// if (!StrUtil.requestParamsNotEmpty(fh)) {
		// fh = "2";// 默认从第二行开始读取
		// }
		// String th = request.getParameter("_toNo");
		// if (excelFile == null) {
		// return "上传文件出错";
		// }

		List<Integer> enableFields = new ArrayList<Integer>();
		Workbook workbook = null;
		try {
			workbook = getWorkbook(excelFile.getInputStream(),
					excelFile.getOriginalFilename());
			for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
				Sheet sheet = workbook.getSheetAt(numSheet);
				if (sheet == null) {
					continue;
				}
				int firstRowIndex = sheet.getFirstRowNum();
				int lastRowIndex = sheet.getLastRowNum();

				// 读取首行 即,表头
				Row firstRow = sheet.getRow(firstRowIndex);
				if (numSheet == 0) {// 只在第一页读取表头，后续页表头需要一致
					for (int i = firstRow.getFirstCellNum(); i <= firstRow
							.getLastCellNum(); i++) {
						Cell cell = firstRow.getCell(i);
						String cellValue = getCellValue(cell, true);
						System.out.print(" " + cellValue + "\t");
						// 告诉程序表头对应的数据该如何存放
						if (fieldMsg.containsKey(cellValue)) {
							String property = fieldMsg.get(cellValue);
							propertyLocations.put(i, property);
							if (StringUtils.startsWithIgnoreCase(cellValue,
									"id")) {
								idFieldList.add(i);
							} else if (StringUtils.startsWithIgnoreCase(
									cellValue, "a")) {
								valueFieldList.add(i);
							}
							enableFields.add(i);
						}
					}
				}
				// 读取数据行
				for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
					Map<Integer, String> singeValue = new HashMap<Integer, String>();
					Row currentRow = sheet.getRow(rowIndex);// 当前行
					int firstColumnIndex = currentRow.getFirstCellNum(); // 首列
					int lastColumnIndex = currentRow.getLastCellNum();// 最后一列
					for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) {
						if (enableFields.contains(columnIndex)) {
							Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
							String currentCellValue = getCellValue(
									currentCell, true);// 当前单元格的值
							System.out.print(currentCellValue + "\t");
							singeValue.put(columnIndex, currentCellValue);
						}
					}
					System.out.println("");
					values.add(singeValue);
				}
				System.out
						.println("======================================================");
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// finally {
		// if (workbook != null) {
		// try {
		// workbook.close();
		// } catch (IOException e) {
		// e.printStackTrace();
		// }
		// }
		// }
	}

	/**
	 * 取单元格的值
	 * 
	 * @param cell
	 *            单元格对象
	 * @param treatAsStr
	 *            为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
	 * @return
	 */
	public static String getCellValue(Cell cell, boolean treatAsStr) {
		if (cell == null) {
			return "";
		}

		if (treatAsStr) {
			// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
			// 加上下面这句，临时把它当做文本来读取
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}

		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

	/***
	 * <pre>
	 * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
	 *   xls:HSSFWorkbook
	 *   xlsx：XSSFWorkbook
	 * @param filePath
	 * @return
	 * @throws IOException
	 * </pre>
	 */
	public static Workbook getWorkbook(InputStream inputStream, String filename)
			throws IOException {
		Workbook workbook = null;
		if (filename.endsWith(EXTENSION_XLS)) {
			workbook = new HSSFWorkbook(inputStream);
		} else if (filename.endsWith(EXTENSION_XLSX)) {
			workbook = new XSSFWorkbook(inputStream);
		}
		return workbook;
	}

	/**
	 * put ins into file
	 * 
	 * @param ins
	 * @param file
	 */
	public static void inputstreamToFile(InputStream ins, File file) {
		try {
			OutputStream os = new FileOutputStream(file);
			int bytesRead = 0;
			byte[] buffer = new byte[8192];
			while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
				os.write(buffer, 0, bytesRead);
			}
			os.close();
			ins.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// recursive search all .java files
	private void search(File path) {
		File[] files = path.listFiles();

		// search all .java files in the sub-directory
		for (File file : files) {
			if (file.isDirectory()) {
				// File dir = file.getAbsoluteFile();
				listFilteredFileName(file);
				search(file);
			}
		}
	}

	// show .java files by directory
	private void listFilteredFileName(File file) {
		String[] files = file.list(new DirFilter("\\w+.java"));
		for (String fileName : files) {
			System.out.println(fileName + " ");
		}
	}
}

// to match a given regex
class DirFilter implements FilenameFilter {

	private Pattern pattern;

	public DirFilter(String regex) {
		pattern = Pattern.compile(regex);
	}

	public boolean accept(File dir, String name) {
		return pattern.matcher(name).matches();
	}
}
